-- created by kumiler
-- on 2022/11/16 15:07
-- DESC
set hive.exec.dynamic.partition=true;            --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=100;         --  每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=100; --  每天生成 60 个分区
insert overwrite table jms_dm.dm_track_without_head_dt partition(dt)
select
    a.create_date,
    a.bill_code,
    a.timeout_type,
    a.scan_network_name,
    a.scan_network_code,
    a.pick_network_name,
    a.pick_network_code,
    a.network_id,
    a.package_inside_charge_weight,
    a.goods_name,
    a.arrival_shipment_no,
    '{{ execution_date | cst_ds }}' as dt
from (select date(latest_scan_date) as create_date,
             a.bill_code       as bill_code,
             CASE
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 12 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 24 * 3600, 'yyyy-MM-dd HH:mm:ss') then 1
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 24 * 3600, 'yyyy-MM-dd HH:mm:ss') and
                      latest_scan_date >= from_unixtime(unix_timestamp() - 36 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 2
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 36 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 48 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 3
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 48 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 72 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 4
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 72 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 96 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 5
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 96 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 120 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 6
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 120 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 144 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 7
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 144 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 240 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 8
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 240 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 9
                 ELSE 0
                 END                              AS timeout_type,
             a.latest_scan_code                   as scan_network_name,
             a.latest_scan_name                   as scan_network_code,
             a.pick_network_name                  as pick_network_name,
             a.pick_network_code                  as pick_network_code,
             c.id                                 as network_id,
             a.package_inside_charge_weight       as package_inside_charge_weight,
             a.goods_name                         as goods_name,
             a.transfer_code                      as arrival_shipment_no
      from jms_dwd.dwd_wide_yl_rt_ssmx_bill_detail_hf a
               left join jms_dim.dim_network_whole_massage c on a.pick_network_code = c.code
      where a.dt >= date_sub('{{ execution_date | cst_ds }}',30) and a.dt<='{{ execution_date | cst_ds }}'              --往前推1个月
        and a.latest_scan_date >= date_sub('{{ execution_date | cst_ds }}',9) and a.latest_scan_date <='{{ execution_date | cst_ds }}' and a.package_inside_charge_weight > '0'
        and a.is_finish = 0 and a.biz_sign_date = '1999-01-01 00:00:00' and latest_scan_type='11') a
where a.timeout_type >= 2
union ALL
select
    a.create_date,
    a.bill_code,
    a.timeout_type,
    a.scan_network_name,
    a.scan_network_code,
    a.pick_network_name,
    a.pick_network_code,
    a.network_id,
    a.package_inside_charge_weight,
    a.goods_name,
    a.arrival_shipment_no,
    '{{ execution_date | cst_ds }}' as dt
from (select date(latest_scan_date) as create_date,
             a.bill_code       as bill_code,
             CASE
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 12 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 24 * 3600, 'yyyy-MM-dd HH:mm:ss') then 1
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 24 * 3600, 'yyyy-MM-dd HH:mm:ss') and
                      latest_scan_date >= from_unixtime(unix_timestamp() - 36 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 2
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 36 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 48 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 3
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 48 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 72 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 4
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 72 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 96 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 5
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 96 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 120 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 6
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 120 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 144 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 7
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 144 * 3600, 'yyyy-MM-dd HH:mm:ss') AND
                      latest_scan_date >= from_unixtime(unix_timestamp() - 240 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 8
                 WHEN latest_scan_date < from_unixtime(unix_timestamp() - 240 * 3600, 'yyyy-MM-dd HH:mm:ss') THEN 9
                 ELSE 0
                 END                              AS timeout_type,
             a.latest_scan_code                   as scan_network_name,
             a.latest_scan_name                   as scan_network_code,
             a.pick_network_name                  as pick_network_name,
             a.pick_network_code                  as pick_network_code,
             c.id                                 as network_id,
             a.package_inside_charge_weight       as package_inside_charge_weight,
             a.goods_name                         as goods_name,
             a.transfer_code                      as arrival_shipment_no,
             b.network_type                       as network_type
      from jms_dwd.dwd_wide_yl_rt_ssmx_bill_detail_hf a
               left join jms_dim.dim_network_whole_massage b on a.latest_scan_code = b.code
               left join jms_dim.dim_network_whole_massage c on a.pick_network_code = c.code
      where a.dt >= date_sub('{{ execution_date | cst_ds }}',30) and a.dt<='{{ execution_date | cst_ds }}'              --往前推1个月
        and a.latest_scan_date >= date_sub('{{ execution_date | cst_ds }}',9) and a.latest_scan_date <='{{ execution_date | cst_ds }}' and a.package_inside_charge_weight > '0'
        and a.is_finish = 0 and a.biz_sign_date = '1999-01-01 00:00:00' and latest_scan_type='4') a
where a.timeout_type >= 2 and a.network_type = 4
    distribute by 1;